<?php
namespace app\admin\controller;

use app\admin\controller\Base;
use think\Db;
use think\helper\Time;
use think\Loader;


class Finance extends Base
{
    /**
     * 奖金列表
     * @return mixed
     */

    public function FinanceList(){
        //条件查询
        $Data = Param();
        if (empty($Data)){
            $Data = [
                'reward_type' => 0,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
                'from' => '',
                'order_id' => '',
            ];

        }

        $this->assign('Data',$Data);
        $Where = [];
        //等级查询
        if($Data['reward_type'] > 0){
            $Where['reward_type'] = $Data['reward_type'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];
        }

        //订单号查询
        if($Data['order_id'] != ''){
            $Where['order_id'] = array('like','%'.filterEmoji($Data['order_id']).'%');

        }

        if($Data['from'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['from']]);
            $Where['from_id'] = $UserInfo['id'];
        }

//        //会员名称模糊查询
//        if($Data['title'] != ''){
//
//            //根据会员账号找出会员ID
//            $UserWhere['title'] = array('like','%'.$Data['title'].'%');
//            $UserList = Db::name('user')->where($UserWhere)->select();
//            $IdArray = [];
//            foreach ($UserList as $k=>$v){
//                $IdArray[] = $UserList[$k]['id'];
//            }
//            $Where['user_id'] = array('in',$IdArray);
//
//        }
        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }


        //总记录条数
        $Count = FinanceCount($Where);
        $this->assign('Count', $Count);

        //会员信息
        $FinanceList = FinanceList($Where);
        $this->assign('FinanceList', $FinanceList['FinanceList']);

        //分页
        $this->assign('Page', $FinanceList['Page']);

        return $this->fetch('FinanceList');
    }


    /**
     * 奖金列表导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function FinanceExport(){

        //条件查询
        $Data = Param();
        $Where = [];
        if (empty($Data)){
            $Data = [
                'reward_type' => 0,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }
        $this->assign('Data',$Data);

        //等级查询
        if($Data['reward_type'] > 0){
            $Where['reward_type'] = $Data['reward_type'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

//        //会员名称模糊查询
//        if($Data['title'] != ''){
//
//            //根据会员账号找出会员ID
//            $UserWhere['title'] = array('like','%'.$Data['title'].'%');
//            $UserList = Db::name('user')->where($UserWhere)->select();
//            $IdArray = [];
//            foreach ($UserList as $k=>$v){
//                $IdArray[] = $UserList[$k]['id'];
//            }
//            $Where['user_id'] = array('in',$IdArray);
//
//        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }

        $xlsData = Db('finance')->where($Where)->select();

        Loader::import('PHPExcel.PHPExcel');
        Loader::import('PHPExcel.PHPExcel.Worksheet.Drawing');
        Loader::import('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E");
        $arrHeader = array('会员账号','来源账号','奖金数量','备注信息','记录时间');

        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };


        //填充表格信息
        foreach($xlsData as $k=>$v){
            $UserInfo = UserInfo(['id'=>$xlsData[$k]['user_id']]);
            $FromInfo = UserInfo(['id'=>$xlsData[$k]['from_id']]);

            $k +=2;
            $objActSheet->setCellValue('A'.$k, $UserInfo['username']." ");
            $objActSheet->setCellValue('B'.$k, $FromInfo['username']." ");
            $objActSheet->setCellValue('C'.$k, $v['num']);
            $objActSheet->setCellValue('D'.$k, $v['remarks']);
            $objActSheet->setCellValue('E'.$k, date('Y-m-d H:i',$v['add_time']));
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);

            $width = array(10,15,20,25,30);
            //设置表格的宽度
            $objActSheet->getColumnDimension('A')->setWidth($width[1]);
            $objActSheet->getColumnDimension('B')->setWidth($width[1]);
            $objActSheet->getColumnDimension('C')->setWidth($width[1]);
            $objActSheet->getColumnDimension('D')->setWidth($width[2]);
            $objActSheet->getColumnDimension('E')->setWidth($width[2]);
        }


        $outfile = "奖金明细列表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter->save('php://output');
    }

    /**
     * 提现列表
     * @return mixed
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function WithdrawList(){
        //条件查询
        $Data = Param();
        if (empty($Data)){
            $Data = [
                'status' => 5,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);
        $Where = [];
        //等级查询
        if($Data['status'] != 5){
            $Where['status'] = $Data['status'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

//        //会员名称模糊查询
//        if($Data['title'] != ''){
//
//            //根据会员账号找出会员ID
//            $UserWhere['title'] = array('like','%'.$Data['title'].'%');
//            $UserList = Db::name('user')->where($UserWhere)->select();
//            $IdArray = [];
//            foreach ($UserList as $k=>$v){
//                $IdArray[] = $UserList[$k]['id'];
//            }
//            $Where['user_id'] = array('in',$IdArray);
//
//        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }


        //总记录条数
        $Count = WithdrawCount($Where);
        $this->assign('Count', $Count);

        //提现信息
        $WithdrawList = WithdrawList($Where);
        $this->assign('WithdrawList', $WithdrawList['WithdrawList']);

        //分页
        $this->assign('Page', $WithdrawList['Page']);

        return $this->fetch('WithdrawList');
    }

    /**
     * 提现通过
     * @return array
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function WithdrawSure(){
        if(IsPost()){
            $ID = Param('id');
            $SaveData['status'] = 1;
            $SaveData['sure_time'] = time();
            if(Db::name('user_withdraw')->where(['id'=>$ID])->update($SaveData)){
                return ['status'=>1,'message'=>'通过成功'];
            }else{
                return ['status'=>0,'message'=>'系统错误，请重试'];
            }
        }
    }

    /**
     * 提现驳回
     * @return array
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function WithdrawBack(){
        if(IsPost()){

            $ID = Param('id');

            //记录信息
            $WithdrawInfo = Db::name('user_withdraw')->where(['id'=>$ID])->find();

            if($WithdrawInfo['status'] != 0){
                return ['status'=>0,'message'=>'该记录也被处理，请勿重复操作'];
            }

            //更新记录状态
            $SaveData['status'] = 2;
            $SaveData['sure_time'] = time();

            if(Db::name('user_withdraw')->where(['id'=>$ID])->update($SaveData)){

                //会员信息
                $UserInfo = UserInfo(['id'=>$WithdrawInfo['user_id']]);

                //返还提现金额
                $UserSave['balance_4'] = $UserInfo['balance_4'] + $WithdrawInfo['num'];
                Db::name('user')->where(['id'=>$UserInfo['id']])->update($UserSave);

                //添加流水明细
                FinanceAdd($UserInfo['id'],$UserInfo['id'],$WithdrawInfo['num'],4,10,'提现驳回');

                return ['status'=>1,'message'=>'驳回成功'];
            }else{
                return ['status'=>0,'message'=>'系统错误，请重试'];
            }
        }
    }

    /**
     * 提现导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function WithdrawExport(){

        //条件查询
        $Data = Param();
        $Where = [];
        if (empty($Data)){
            $Data = [
                'status' => 5,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);;
        //等级查询
        if($Data['status'] != 5){
            $Where['status'] = $Data['status'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }

        $xlsData = Db('user_withdraw')->where($Where)->select();

        Loader::import('PHPExcel.PHPExcel');
        Loader::import('PHPExcel.PHPExcel.Worksheet.Drawing');
        Loader::import('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F,G,H,I");
        $arrHeader = array('会员账号','提现数量','手续费用','实际到账','提至','收款信息','申请时间','处理时间','处理状态');

        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };


        //填充表格信息
        foreach($xlsData as $k=>$v){
            $UserInfo = UserInfo(['id'=>$xlsData[$k]['user_id']]);
            if(!empty($xlsData[$k]['status'])){
               $SureTime = date('Y-m-d H:i',$v['sure_time']);
            }else{
                $SureTime = '';
            }
            if($xlsData[$k]['status'] == 0){
                $Status = '待处理';
            }elseif($xlsData[$k]['status'] == 1){
                $Status = '已通过';
            }elseif($xlsData[$k]['status'] == 2){
                $Status = '已驳回';
            }

            if($xlsData[$k]['to_type'] == 1){
                $To = '银行卡';
                $Str = '开户银行：'.$v['bank_name'].' 开户支行：'.$v['bank_open'].' 银行卡号：'.$v['bank_num'].' 开户姓名：'.$v['bank_user'];
            }elseif($xlsData[$k]['to_type'] == 2){
                $To = '支付宝';
                $Str = '支付宝收款码';
            }elseif($xlsData[$k]['to_type'] == 3){
                $To = '微信';
                $Str = '微信收款码';
            }

            $k +=2;
            $objActSheet->setCellValue('A'.$k, $UserInfo['username']." ");
            $objActSheet->setCellValue('B'.$k, $v['num']);
            $objActSheet->setCellValue('C'.$k, $v['fees']);
            $objActSheet->setCellValue('D'.$k, $v['real_num']);
            $objActSheet->setCellValue('E'.$k, $To);
            $objActSheet->setCellValue('F'.$k, $Str);
            $objActSheet->setCellValue('G'.$k, date('Y-m-d H:i',$v['add_time']));
            $objActSheet->setCellValue('H'.$k, $SureTime);
            $objActSheet->setCellValue('I'.$k, $Status);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);

            $width = array(10,15,20,25,30);
            //设置表格的宽度
            $objActSheet->getColumnDimension('A')->setWidth($width[1]);
            $objActSheet->getColumnDimension('B')->setWidth($width[1]);
            $objActSheet->getColumnDimension('C')->setWidth($width[1]);
            $objActSheet->getColumnDimension('D')->setWidth($width[2]);
            $objActSheet->getColumnDimension('E')->setWidth($width[2]);
            $objActSheet->getColumnDimension('F')->setWidth($width[2]);
            $objActSheet->getColumnDimension('G')->setWidth($width[2]);
            $objActSheet->getColumnDimension('H')->setWidth($width[2]);
            $objActSheet->getColumnDimension('I')->setWidth($width[2]);
        }


        $outfile = "提现明细列表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter->save('php://output');
    }

    /**
     * 充值记录
     * @return mixed
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function RechargeList(){
        //条件查询
        $Data = Param();
        if (empty($Data)){
            $Data = [
                'status' => 5,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);
        $Where = [];
        //等级查询
        if($Data['status'] != 5){
            $Where['status'] = $Data['status'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }


        //总记录条数
        $Count = RechargeCount($Where);
        $this->assign('Count', $Count);

        //提现信息
        $RechargeList = RechargeList($Where);
        $this->assign('RechargeList', $RechargeList['RechargeList']);

        //分页
        $this->assign('Page', $RechargeList['Page']);

        return $this->fetch('RechargeList');
    }

    /**
     * 充值通过
     * @return array
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function RechargeSure(){
        if(IsPost()){

            $ID = Param('id');

            //记录信息
            $RechargeInfo = Db::name('user_recharge')->where(['id'=>$ID])->find();

            if($RechargeInfo['status'] != 0){
                return ['status'=>0,'message'=>'该记录也被处理，请勿重复操作'];
            }

            //系统参数
            $Config = Config();
            //更新记录状态
            $SaveData['status'] = 1;
            $SaveData['sure_time'] = time();

            if(Db::name('user_recharge')->where(['id'=>$ID])->update($SaveData)){
                //会员信息
                $UserInfo = UserInfo(['id'=>$RechargeInfo['user_id']]);

                //根据充值类型增加对应余额
                $UserSave['balance_'.$RechargeInfo['type']] = $UserInfo['balance_'.$RechargeInfo['type']] + $RechargeInfo['num']*$Config['chong'];

                Db::name('user')->where(['id'=>$UserInfo['id']])->update($UserSave);

                //添加流水明细
                FinanceAdd($UserInfo['id'],$UserInfo['id'],$RechargeInfo['num'],$RechargeInfo['type'],11,'会员充值');

                return ['status'=>1,'message'=>'通过成功'];
            }else{
                return ['status'=>0,'message'=>'系统错误，请重试'];
            }
        }
    }


    /**
     * 充值驳回
     * @return array
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function RechargeBack(){
        if(IsPost()){
            $ID = Param('id');
            $SaveData['status'] = 2;
            $SaveData['sure_time'] = time();
            if(Db::name('user_recharge')->where(['id'=>$ID])->update($SaveData)){
                return ['status'=>1,'message'=>'驳回成功'];
            }else{
                return ['status'=>0,'message'=>'系统错误，请重试'];
            }
        }
    }


    /**
     * 充值导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function RechargeExport(){

        //条件查询
        $Data = Param();
        $Where = [];
        if (empty($Data)){
            $Data = [
                'status' => 5,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);;
        //等级查询
        if($Data['status'] != 5){
            $Where['status'] = $Data['status'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }

        $xlsData = Db('user_recharge')->where($Where)->select();

        Loader::import('PHPExcel.PHPExcel');
        Loader::import('PHPExcel.PHPExcel.Worksheet.Drawing');
        Loader::import('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E");
        $arrHeader = array('会员账号','充值数量','充值时间','处理时间','处理状态');

        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };


        //填充表格信息
        foreach($xlsData as $k=>$v){
            $UserInfo = UserInfo(['id'=>$xlsData[$k]['user_id']]);
            if(!empty($xlsData[$k]['status'])){
                $SureTime = date('Y-m-d H:i',$v['sure_time']);
            }else{
                $SureTime = '';
            }
            if($xlsData[$k]['status'] == 0){
                $Status = '待处理';
            }elseif($xlsData[$k]['status'] == 1){
                $Status = '已通过';
            }elseif($xlsData[$k]['status'] == 2){
                $Status = '已驳回';
            }

            $k +=2;
            $objActSheet->setCellValue('A'.$k, $UserInfo['username'].' ');
            $objActSheet->setCellValue('B'.$k, $v['num']);
            $objActSheet->setCellValue('C'.$k, date('Y-m-d H:i',$v['add_time']));
            $objActSheet->setCellValue('D'.$k, $SureTime);
            $objActSheet->setCellValue('E'.$k, $Status);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);

            $width = array(10,15,20,25,30);
            //设置表格的宽度
            $objActSheet->getColumnDimension('A')->setWidth($width[1]);
            $objActSheet->getColumnDimension('B')->setWidth($width[1]);
            $objActSheet->getColumnDimension('C')->setWidth($width[1]);
            $objActSheet->getColumnDimension('D')->setWidth($width[2]);
            $objActSheet->getColumnDimension('E')->setWidth($width[2]);
        }


        $outfile = "充值明细列表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter->save('php://output');
    }




    /**
     * 财务统计
     * @return mixed
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function FinancialStatistics(){

        //条件查询
        $Data = Param();
        if (empty($Data)){
            $Data = [
                'status' => 5,
                'username' => '',
                'title' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);

            $UserMap['open_time'] = array('egt',$BeginTime);
            $TotalFinanceMap['add_time'] = array('egt',$BeginTime);
            $WithdrawTotalMap['add_time'] = array('egt',$BeginTime);
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);

            $UserMap['open_time'] = array('elt',$EndTime);
            $TotalFinanceMap['add_time'] = array('elt',$EndTime);
            $WithdrawTotalMap['add_time'] = array('elt',$EndTime);
    }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);

            $UserMap['open_time'] = array(array('egt',$BeginTime),array('elt',$EndTime));
            $TotalFinanceMap['add_time'] = array(array('egt',$BeginTime),array('elt',$EndTime));
            $WithdrawTotalMap['add_time'] = array(array('egt',$BeginTime),array('elt',$EndTime));
        }


        //收入
        $LevelList = LevelList();
        $TotalIncome = 0;
        foreach($LevelList as $k=>$v){
            $UserMap['level'] = $LevelList[$k]['id'];
            $UserMap['status'] = 1;
            $Income = UserCount($UserMap) * $LevelList[$k]['amount'];
            $TotalIncome+= $Income;
        }
        $this->assign('TotalIncome',$TotalIncome);

        //奖金

        $TotalFinanceMap['reward_type'] = array('in','1,2');
        $TotalFinance = Db::name('finance')->where($TotalFinanceMap)->sum('num');
        $this->assign('TotalFinance',$TotalFinance);

        //拨比率
        if($TotalIncome == 0 ){
            $Proportion = $TotalFinance * 100;
        }else{
            $Proportion = round($TotalFinance / $TotalIncome ,4)*100;
        }
        $this->assign('Proportion',$Proportion);

        //提现金额
        $WithdrawTotalMap['status'] = 1;
        $WithdrawTotal = Db::name('user_withdraw')->where($WithdrawTotalMap)->sum('num');
        $this->assign('WithdrawTotal',$WithdrawTotal);

        //余额
        $UserBalance = Db::name('user')->sum('balance_1');  //所有用户余额
        $Withdraw = Db::name('user_withdraw')->where(['status'=>0])->sum('num');    //未处理的提现
        $TotalBalance = $UserBalance + $Withdraw;
        $this->assign('TotalBalance',$TotalBalance);

        //会员激活
        $UserOpen = UserCount(['status'=>1]);
        $this->assign('UserOpen',$UserOpen);

        //会员激活
        $UserCount = UserCount();
        $this->assign('UserCount',$UserCount);

        return $this->fetch('FinancialStatistics');
    }


    /**
     * 定存记录
     * @return mixed
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function DepositList(){

        //条件查询
        $Data = Param();
        if (empty($Data)){
            $Data = [
                'cycle' => 0,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }

        $this->assign('Data',$Data);
        $Where = [];
        //定存周期查询
        if($Data['cycle'] > 0){
            $Where['cycle'] = $Data['cycle'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];
        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }


        //总记录条数
        $Count = DepositCount($Where);
        $this->assign('Count', $Count);

        //会员信息
        $DepositList = DepositList($Where);
        $this->assign('DepositList', $DepositList['DepositList']);

        //分页
        $this->assign('Page', $DepositList['Page']);

        return $this->fetch('DepositList');
    }


    /**
     * 定存记录导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function DepositExport(){

        //条件查询
        $Data = Param();
        $Where = [];
        if (empty($Data)){
            $Data = [
                'cycle' => 0,
                'username' => '',
                'begin_time' => '',
                'end_time' => '',
            ];

        }
        $this->assign('Data',$Data);

        //等级查询
        if($Data['cycle'] > 0){
            $Where['cycle'] = $Data['cycle'];
        }

        //会员账号查询
        if($Data['username'] != ''){

            //根据会员账号找出会员ID
            $UserInfo = UserInfo(['username'=>$Data['username']]);
            $Where['user_id'] = $UserInfo['id'];

        }

//        //会员名称模糊查询
//        if($Data['title'] != ''){
//
//            //根据会员账号找出会员ID
//            $UserWhere['title'] = array('like','%'.$Data['title'].'%');
//            $UserList = Db::name('user')->where($UserWhere)->select();
//            $IdArray = [];
//            foreach ($UserList as $k=>$v){
//                $IdArray[] = $UserList[$k]['id'];
//            }
//            $Where['user_id'] = array('in',$IdArray);
//
//        }

        //时间查询
        if($Data['begin_time'] != '' && $Data['end_time'] == ''){   //只有开始时间没有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $Where['add_time'] = array('egt',$BeginTime );
        }

        if($Data['begin_time'] == '' && $Data['end_time'] != ''){   //只有结束时间没有开始时间
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array('elt',$EndTime );
        }

        if($Data['begin_time'] != '' && $Data['end_time'] != ''){   //有开始时间有结束时间
            $BeginTime = strtotime($Data['begin_time']);
            $EndTime = strtotime($Data['end_time']);
            $Where['add_time'] = array(array('egt',$BeginTime ),array('elt',$EndTime ));
        }

        $xlsData = Db('user_deposit')->where($Where)->select();

        Loader::import('PHPExcel.PHPExcel');
        Loader::import('PHPExcel.PHPExcel.Worksheet.Drawing');
        Loader::import('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F");
        $arrHeader = array('会员账号','当前等级','理财金额','剩余金额','定存时间','定存周期');

        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };


        //填充表格信息
        foreach($xlsData as $k=>$v){
            $UserInfo = UserInfo(['id'=>$xlsData[$k]['user_id']]);

            $LevelInfo = LevelInfo(['id'=>$UserInfo['level']]);

            if($xlsData[$k]['cycle'] == 1){
                $CycleTime = '半年期';
            }elseif($xlsData[$k]['cycle'] == 2){
                $CycleTime = '一年期';
            }
            elseif($xlsData[$k]['cycle'] == 3){
                $CycleTime = '二年期';
            }
            elseif($xlsData[$k]['cycle'] == 4){
                $CycleTime = '三年期';
            }
            $k +=2;
            $objActSheet->setCellValue('A'.$k, $UserInfo['username']." ");
            $objActSheet->setCellValue('B'.$k, $LevelInfo['name']);
            $objActSheet->setCellValue('C'.$k, $v['num']);
            $objActSheet->setCellValue('D'.$k, $v['surplus_num']);
            $objActSheet->setCellValue('E'.$k, date('Y-m-d H:i',$v['add_time']));
            $objActSheet->setCellValue('F'.$k, $CycleTime);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);

            $width = array(10,15,20,25,30);
            //设置表格的宽度
            $objActSheet->getColumnDimension('A')->setWidth($width[1]);
            $objActSheet->getColumnDimension('B')->setWidth($width[1]);
            $objActSheet->getColumnDimension('C')->setWidth($width[1]);
            $objActSheet->getColumnDimension('D')->setWidth($width[2]);
            $objActSheet->getColumnDimension('E')->setWidth($width[2]);
            $objActSheet->getColumnDimension('F')->setWidth($width[2]);
        }


        $outfile = "定存记录明细列表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter->save('php://output');
    }
}